﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data.Common;
using System.Data;
using System.Windows.Forms;
using PriErp.Tools;
using System.Reflection;
using System.ComponentModel;

namespace PriErp.Data
{
    /// <summary>
    /// 说明：这是一个针对System.Data.SQLite的数据库常规操作封装的通用类。
    /// Version:0.1
    /// </summary>
    public static class DbHelperSQLite
    {
        //数据库连接字符串(本程序使用固定位置)   
        static  string connectionString = String.Format("Data Source={0}\\db\\perp.db3;Pooling=False", Application.StartupPath);
        #region 基础公开方法
        /// <summary>
        /// 读取一条数据信息填充到实体
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="sqlString">查询语句</param>
        /// <param name="parameters">查询参数</param>
        /// <param name="model">对象实体</param>
        public static void FillDatatoOneModel<T>(String sqlString, SQLiteParameter[] parameters, T model)
        {
            using (SQLiteDataReader sr = ExecuteReader(sqlString, parameters))
            {
                Type vType = model.GetType();
                if (sr.Read())
                {
                    for (int i = 0; i < sr.FieldCount; i++)
                    {
                        if (!sr.IsDBNull(i))
                        {
                            String name = sr.GetName(i);
                            PropertyInfo info = vType.GetProperty(name);
                            if (info != null)
                                info.SetValue(model, sr.GetValue(i), null);
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 根据需求生成SqlParameter
        /// by denuvead 12-11-12;edit:12-11-16
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ParamName">名称</param>
        /// <param name="DbType">数据类型</param>
        /// <param name="Size">参数大小，无则填写小于1的值</param>
        /// <param name="Val">参数值</param>
        /// <returns></returns>
        public static SQLiteParameter MakeParams<T>(string ParamName, DbType DbType, int Size, T Val)
        {
            SQLiteParameter param = Size > 0 ? new SQLiteParameter(ParamName, DbType, Size)
                                             : new SQLiteParameter(ParamName, DbType);
            //TODO：后期看软件需求是否需要对val作一定限制 
            param.Value = Val;
            return param;
        }
        #endregion

        #region 公用方法
        /// <summary>
        /// 构建SQLiteCommand信息
        /// </summary>
        /// <param name="cmd">需要构建的SQLiteCommand</param>
        /// <param name="conn">SQLiteConnection</param>
        /// <param name="trans">SQLiteTransaction</param>
        /// <param name="cmdText">SQL语句</param>
        /// <param name="cmdParms">SQL参数</param>
        static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, string cmdText, SQLiteParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;SQLLite不支持存储过程
            cmd.Parameters.Clear();//添加参数前清空参数
            if (cmdParms != null)
            {
                foreach (SQLiteParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
        #endregion

        #region  执行简单SQL语句
        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
                {
                    connection.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 向数据库里插入图像格式的字段(仅仅是示例，暂无功能)
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
        /// <returns>影响的记录数</returns>
        static public int ExecuteSqlInsertImg(string strSQL, byte[] fs)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (SQLiteCommand cmd = new SQLiteCommand(strSQL, connection))
                {
                    SQLiteParameter myParameter = new SQLiteParameter("@fs", System.Data.DbType.Binary); //to be check
                    myParameter.Value = fs;
                    cmd.Parameters.Add(myParameter);
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>
        /// <returns>返回是否执行成功</returns>
        public static bool ExecuteSqlTran(List<string> SQLStringList)
        {
            bool v = false;
            using (SQLiteConnection conn = new SQLiteConnection(connectionString))
            {
                using (SQLiteTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SQLStringList.ForEach(item =>
                        {
                            SQLiteCommand cmd = new SQLiteCommand();
                            PrepareCommand(cmd, conn, trans, item, null);
                            cmd.ExecuteNonQuery();
                        });
                        trans.Commit();
                        v = true;
                    }
                    catch
                    {
                        trans.Rollback();
                    }
                }
            }
            return v;
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回单个查询结果（object）
        /// </summary>
        /// <param name="SQLString">查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
                {
                    connection.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }
        /// <summary>
        /// 执行查询语句，返回SQLiteDataReader
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SQLiteDataReader</returns>
        static public SQLiteDataReader ExecuteReader(string strSQL)
        {
            SQLiteConnection connection = new SQLiteConnection(connectionString);
            SQLiteCommand cmd = new SQLiteCommand(strSQL, connection);
            connection.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataTable</returns>
        static public DataTable Query(string SQLString)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(SQLString, connection))
                {
                    DataTable data = new DataTable();
                    adapter.Fill(data);
                    return data;
                }
            }
        }

        /// <summary>
        ///  执行查询语句，返回分页数量DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <param name="iPageSize">分页数量</param>
        /// <param name="iPageIndex">页码</param>
        /// <returns>DataTable</returns>
        static public DataTable PagedQuery(string SQLString, int iPageSize, int iPageIndex)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                int iStartRecord = iPageIndex * iPageSize;
                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(SQLString, connection))
                {
                    DataTable data = new DataTable();
                    adapter.Fill(iStartRecord, iPageSize, data);
                    return data;
                }
            }
        }
        #endregion

        #region 执行带参数的SQL语句
        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        static public int ExecuteSql(string SQLString, params SQLiteParameter[] cmdParms)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
                {
                    PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回单个查询结果（object）
        /// </summary>
        /// <param name="SQLString">查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        static public object GetSingle(string SQLString, params SQLiteParameter[] cmdParms)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                    return cmd.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回SQLiteDataReader
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SQLiteDataReader</returns>
        static public SQLiteDataReader ExecuteReader(string SQLString, params SQLiteParameter[] cmdParms)
        {
            SQLiteConnection connection = new SQLiteConnection(connectionString);
            SQLiteCommand cmd = new SQLiteCommand();
            PrepareCommand(cmd, connection, null, SQLString, cmdParms);
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 执行查询语句，返回DataTable
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <param name="cmdParms">参数列表</param>
        /// <returns>DataTable</returns>
        static public DataTable Query(string SQLString, params SQLiteParameter[] cmdParms)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                    //DataSet ds = new DataSet();
                    //adapter.Fill(ds, "ds");
                    //cmd.Parameters.Clear();
                    //return ds;
                    DataTable data = new DataTable();
                    adapter.Fill(data);
                    return data;
                }
            }
        }

        /// <summary>
        ///  执行查询语句，返回分页数量DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <param name="iPageSize">分页数量</param>
        /// <param name="iPageIndex">页码</param>
        /// <param name="cmdParms">参数列表</param> 
        /// <returns>DataTable</returns>
        static public DataTable PagedQuery(string SQLString, int iPageSize, int iPageIndex, params SQLiteParameter[] cmdParms)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                    int iStartRecord = iPageIndex * iPageSize;
                    SQLiteDataAdapter command = new SQLiteDataAdapter(cmd);
                    DataTable data = new DataTable();
                    command.Fill(iStartRecord, iPageSize, data);
                    return data;
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的词典（key为sql语句，value是该语句的SQLiteParameter[]）</param>
        /// <returns>是否成功执行</returns>
        static public bool ExecuteSqlTran(Dictionary<string,SQLiteParameter[]> SQLStringList)
        {
            bool v = false;
            using (SQLiteConnection conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                using (SQLiteTransaction trans = conn.BeginTransaction())
                {
                    SQLiteCommand cmd = new SQLiteCommand();
                    try
                    {
                        //循环
                        foreach (string currSql in SQLStringList.Keys)
                        {
                            PrepareCommand(cmd, conn, trans, currSql, SQLStringList[currSql]);
                            cmd.ExecuteNonQuery();
                            trans.Commit();
                        }
                        v = true;
                    }
                    catch
                    {
                        trans.Rollback();
                    }
                }
            }
            return v;
        }
        #endregion
    }
}